package com.motu.vertx.module.utility.mysql;


import io.vertx.core.*;
import io.vertx.core.buffer.Buffer;
import io.vertx.core.json.JsonArray;
import io.vertx.core.json.JsonObject;
import io.vertx.mysqlclient.MySQLBuilder;
import io.vertx.mysqlclient.MySQLClient;
import io.vertx.mysqlclient.MySQLConnectOptions;
import io.vertx.sqlclient.Pool;
import io.vertx.sqlclient.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.concurrent.TimeUnit;

public class MysqlTool {
    private static final Logger logger = LoggerFactory.getLogger("MysqlTool");

    /**
     * 创建数据库连接对象
     * @param vertx
     * @param appConfig 外部参数配置对象
     * @param configKey 参数key名
     * @return 数据库连接对象
     */
    public static Pool createMySQLPool(Vertx vertx, JsonObject appConfig, String configKey) {
        if (!appConfig.containsKey(configKey) || appConfig.getJsonObject(configKey).isEmpty()) {
            return null;
        }
        logger.info("createMySQLPool configKey:{}", configKey);
        return MySQLBuilder
                .pool()
                .with(getMySQLPoolOptions(appConfig, configKey))
                .connectingTo(getMySQLConnectOptions(appConfig, configKey))
                .using(vertx)
                .build();
    }

    /***
     * 获取数据库连接池基础配置
     * @param appConfig 外部参数配置对象
     * @param configKey 参数key名
     * @return 配置
     */
    public static MySQLConnectOptions getMySQLConnectOptions(JsonObject appConfig, String configKey){
        MySQLConnectOptions connectOptions = new MySQLConnectOptions();
        if (appConfig.containsKey(configKey)) {
            JsonObject mysqlConfig = appConfig.getJsonObject(configKey);
            connectOptions.setPort(mysqlConfig.getInteger("port"));
            connectOptions.setHost(mysqlConfig.getString("host"));
            connectOptions.setDatabase(mysqlConfig.getString("database"));
            connectOptions.setUser(mysqlConfig.getString("user"));
            connectOptions.setPassword(mysqlConfig.getString("password"));
            // 设置字符集，默认是utf8mb4
            if (mysqlConfig.containsKey("charset")){
                connectOptions.setCharset(mysqlConfig.getString("charset"));
            }
        }
        return connectOptions;
    }

    /***
     * 获取数据库连接池大小配置
     * @param appConfig 外部参数配置对象
     * @param configKey 参数key名
     * @return 配置
     */
    public static PoolOptions getMySQLPoolOptions(JsonObject appConfig, String configKey){
        PoolOptions poolOptions = new PoolOptions();
        if (appConfig.containsKey(configKey)) {
            JsonObject mysqlConfig = appConfig.getJsonObject(configKey);
            poolOptions.setMaxSize(mysqlConfig.getInteger("pollsize"));
            // 设置mysql连接池闲置超时时间
            if (mysqlConfig.containsKey("pool_idle_timeout")){
                poolOptions.setIdleTimeout(mysqlConfig.getInteger("pool_idle_timeout"));// 默认单位秒
            }
        }
        return poolOptions;
    }

    /***
     * 将mysql查询出来的row转换成JsonObject
     * @param row
     * @return
     */
    public static JsonObject getRowObject(Row row) {
        JsonObject obj = new JsonObject();
        for (int i = 0; i < row.size(); i++) {
            Object value = row.getValue(i);
            if (value instanceof Buffer){
                Buffer buffer = (Buffer) value;
                obj.put(row.getColumnName(i), buffer.getBytes());
            } else if (value instanceof LocalDate){
                LocalDate localDate = (LocalDate) value;
                obj.put(row.getColumnName(i), localDate.toString());
            } else if (value instanceof LocalDateTime){
                LocalDateTime localDateTime = (LocalDateTime) value;
                obj.put(row.getColumnName(i), localDateTime.toString());
            } else {
                obj.put(row.getColumnName(i), value);
            }
        }
        return obj;
    }

    /**
     * 将mysql查询出来的结果集转换成JsonArray
     *
     * @param rows
     * @return
     */
    public static JsonArray getRowsArray(RowSet<Row> rows) {
        JsonArray array = new JsonArray();
        Iterator<Row> it = rows.iterator();
        while (it.hasNext()) {
            array.add(getRowObject(it.next()));
        }
        return array;
    }

    /***
     * 执行数据库操作
     */
    private static void execute(Pool jdbcClient, String sql, JsonArray params, Handler<AsyncResult<RowSet<Row>>> handler) {
        jdbcClient.getConnection(res -> {
            if (res.succeeded()) {
                SqlConnection connection = res.result();
                connection.prepare(sql, ar -> {
                    if (ar.succeeded()) {
                        PreparedStatement preparedStatement = ar.result();
                        preparedStatement.query().execute(Tuple.tuple(params.getList()), ar2 -> {
                            if (ar2.succeeded()) {
                                RowSet<Row> rows = ar2.result();
                                handler.handle(Future.succeededFuture(rows));
                            } else {
//								logger.error("----############---- sql:"+sql+" params:"+params.toString()+" "+ar2.cause().getMessage());
                                handler.handle(Future.failedFuture(ar2.cause().getMessage()));
                            }
                            preparedStatement.close();
                            connection.close();
                        });
                    } else {
                        logger.error("----############---- prepare error sql:" + sql + " params:" + params.toString() + " " + ar.cause().getMessage());
                        handler.handle(Future.failedFuture(ar.cause().getMessage()));
                        PreparedStatement preparedStatement = ar.result();
                        if (preparedStatement != null){
                            preparedStatement.close();
                        }
                        connection.close();
                    }
                });
            } else {
                logger.error("----############---- getConnection error sql:" + sql + " params:" + params.toString() + " " + res.cause().getMessage());
                handler.handle(Future.failedFuture(res.cause().getMessage()));
            }
        });
    }

    /***
     * 查询数据库，返回第一条记录
     * @param jdbcClient
     * @param sql
     * @param params
     * @param handler
     */
    public static void queryJsonObjectFromDb(Pool jdbcClient, String sql, JsonArray params, Handler<AsyncResult<JsonObject>> handler) {
        jdbcClient
                .preparedQuery(sql)
                .execute(Tuple.tuple(params.getList()), ar -> {
                    if (ar.succeeded()) {
                        RowSet<Row> rows = ar.result();
                        if (rows.size() == 0) {
                            handler.handle(Future.succeededFuture(null));
                        } else {
                            Row firstRow = rows.iterator().next();
                            handler.handle(Future.succeededFuture(getRowObject(firstRow)));
                        }
                    } else {
                        handler.handle(Future.failedFuture(ar.cause().getMessage()));
                    }
                });
    }

    /***
     * 查询数据库，返回第一条记录
     * @param jdbcClient
     * @param sql
     * @param params
     */
    public static Future<JsonObject> queryJsonObjectFromDb(Pool jdbcClient, String sql, JsonArray params) {
        Promise<JsonObject> promise = Promise.promise();
        queryJsonObjectFromDb(jdbcClient, sql, params, promise);
        return promise.future();
    }

    public static void queryJsonArrayFromDb(Pool jdbcClient, String sql, JsonArray params, Handler<AsyncResult<JsonArray>> handler) {
        jdbcClient
                .preparedQuery(sql)
                .execute(Tuple.tuple(params.getList()), ar -> {
                    if (ar.succeeded()) {
                        RowSet<Row> rows = ar.result();
                        if (rows.size() == 0) {
                            handler.handle(Future.succeededFuture(new JsonArray()));
                        } else {
                            handler.handle(Future.succeededFuture(getRowsArray(rows)));
                        }
                    } else {
                        handler.handle(Future.failedFuture(ar.cause().getMessage()));
                    }
                });
    }

    public static Future<JsonArray> queryJsonArrayFromDb(Pool jdbcClient, String sql, JsonArray params) {
        Promise<JsonArray> fut = Promise.promise();
        jdbcClient
                .preparedQuery(sql)
                .execute(Tuple.tuple(params.getList()), ar -> {
                    if (ar.succeeded()) {
                        RowSet<Row> rows = ar.result();
                        if (rows.size() == 0) {
                            fut.complete(new JsonArray());
                        } else {
                            JsonArray array = getRowsArray(rows);
                            fut.complete(array);
                        }
                    } else {
                        fut.fail(ar.cause().getMessage());
                    }
                });
        return fut.future();
    }


    /***
     * 更新到数据库,返回更新成功的条数（3.9.5版本会有问题，3.9.8版本测试了正常）
     * @param jdbcClient
     * @param sql
     * @param params
     * @param handler
     */
    public static void updateToDb(Pool jdbcClient, String sql, JsonArray params, Handler<AsyncResult<Object>> handler) {
        jdbcClient
                .preparedQuery(sql)
                .execute(Tuple.tuple(params.getList()), ar -> {
                    if (ar.succeeded()) {
                        RowSet<Row> rows = ar.result();
                        handler.handle(Future.succeededFuture(rows.rowCount()));
                    } else {
                        handler.handle(Future.failedFuture(ar.cause().getMessage()));
                    }
                });
    }

    /***
     * 更新到数据库,返回更新成功的条数（3.9.5版本会有问题，3.9.8版本测试了正常）
     * @param jdbcClient
     * @param sql
     * @param params
     */
    public static Future<Object> updateToDb(Pool jdbcClient, String sql, JsonArray params) {
        Promise<Object> fut = Promise.promise();
        jdbcClient
                .preparedQuery(sql)
                .execute(Tuple.tuple(params.getList()), ar -> {
                    if (ar.succeeded()) {
                        RowSet<Row> rows = ar.result();
                        fut.complete(Future.succeededFuture(rows.rowCount()));
                    } else {
                        logger.info(" fail ###" + ar.cause().getMessage());
                        fut.complete(ar.cause().getMessage());
                    }
                });
        return fut.future();
    }

    /***
     * 插入数据库并取回自增id
     * @param jdbcClient
     * @param sql
     * @param params
     * @return
     */
    public static void insertAndGetID(Pool jdbcClient, String sql, JsonArray params, Handler<AsyncResult<Long>> handler) {
        jdbcClient
                .preparedQuery(sql)
                .execute(Tuple.tuple(params.getList()), ar -> {
                    if (ar.succeeded()) {
                        long lastInsertId = 0L;
                        try {
                            RowSet<Row> rows = ar.result();
                            lastInsertId = rows.property(MySQLClient.LAST_INSERTED_ID);
                        } catch (Exception e) {

                        }
                        handler.handle(Future.succeededFuture(lastInsertId));
                    } else {
                        handler.handle(Future.failedFuture(ar.cause().getMessage()));
                    }
                });
    }

    /***
     * 插入数据库并取回自增id
     * @param jdbcClient
     * @param sql
     * @param params
     * @return
     */
    public static Future<Long> insertAndGetID(Pool jdbcClient, String sql, JsonArray params) {
        Promise<Long> promise = Promise.promise();
        insertAndGetID(jdbcClient, sql, params, promise);
        return promise.future();
    }

    /***
     * 批量更新到数据库,如果执行成功，返回更新成功的条数
     * @param jdbcClient
     * @param sql
     * @param paramsList 必须是JsonArray的列表
     * @param handler
     */
    public static void batchUpdateToDb(Pool jdbcClient, String sql, List<JsonArray> paramsList, Handler<AsyncResult<Integer>> handler) {
        List<Tuple> batch = new ArrayList<>();
        for (JsonArray params : paramsList) {
            batch.add(Tuple.tuple(params.getList()));
        }
        jdbcClient.preparedQuery(sql).executeBatch(batch, res -> {
            if (res.succeeded()) {
                RowSet<Row> rows = res.result();
                handler.handle(Future.succeededFuture(rows.rowCount()));
            } else {
                logger.error("----############---- sql:" + sql + " paramsList:" + paramsList.toString() + " " + res.cause().getMessage());
                res.cause().printStackTrace();
                handler.handle(Future.failedFuture(res.cause().getMessage()));
            }
        });
    }

    /***
     * 批量更新到数据库,如果执行成功，返回1
     * @param jdbcClient
     * @param sql
     * @param paramsList
     */
    public static Future<Object> batchUpdateToDb(Pool jdbcClient, String sql, List<JsonArray> paramsList) {
        Promise<Object> fut = Promise.promise();
        List<Tuple> batch = new ArrayList<>();
        for (JsonArray params : paramsList) {
            batch.add(Tuple.tuple(params.getList()));
        }
        jdbcClient.preparedQuery(sql).executeBatch(batch, res -> {
            if (res.succeeded()) {
                RowSet<Row> rows = res.result();
                fut.complete(Future.succeededFuture(rows.rowCount()));
            } else {
                logger.error("----############---- sql:" + sql + " paramsList:" + paramsList.toString() + " " + res.cause().getMessage());
                res.cause().printStackTrace();
                fut.complete(res.cause().getMessage());
            }
        });
        return fut.future();
    }

    //	/***
    //	 * 获取单条数据，如"select id,username from userinfo where id = 1",返回的是不带字段名的array[1,"ibm"],如果搜索不到则返回null
    //	 * @param jdbcClient
    //	 * @param sql
    //	 * @param params
    //	 * @param handler
    //	 */
    //	public static void querySingleFromDb(JDBCClient jdbcClient, String sql, JsonArray params, Handler<AsyncResult<JsonArray>> handler) {
    //		jdbcClient.querySingleWithParams(sql, params, res->{
    //			if(res.succeeded()){
    //				JsonArray array = res.result();
    //				handler.handle(Future.succeededFuture(array));
    //			}else{
    //				logger.error("----############---- sql:"+sql+" params:"+params.toString()+" "+res.cause().getMessage());
    //				res.cause().printStackTrace();
    //				handler.handle(Future.failedFuture(res.cause().getMessage()));
    //			}
    //		});
    //	}

//	/***
//	 * 先检查是否存在表，再查询数据库，并返回一个future
//	 * @param jdbcClient
//	 * @param sql
//	 * @param params
//	 * @return
//	 */
//	public static Future<JsonArray> checkQueryJsonArrayFromDb(Pool jdbcClient, String tableName, String sql, JsonArray params) {
//		Promise<JsonArray> fut = Promise.promise();
//		String check_sql = "SELECT table_name FROM information_schema.TABLES WHERE table_name = '" + tableName + "';";
//		jdbcClient.preparedQuery(check_sql).execute(checkRes->{
//			if(checkRes.succeeded()){
//				RowSet<Row> checkRows = checkRes.result();
//				if (checkRows.size() == 0) {
//					logger.error("----############---- sql: Table "+tableName+" doesn't exist");
//					fut.complete(new JsonArray());
//				} else {
//					jdbcClient.preparedQuery(sql).execute(Tuple.tuple(params.getList()), res->{
//						if (res.succeeded()) {
//							RowSet<Row> rows = res.result();
//							fut.complete(getRowsArray(rows));
//						} else {
//							logger.error("----############---- sql:"+sql+" params:"+params.toString()+" "+res.cause().getMessage());
//							res.cause().printStackTrace();
//							fut.fail(res.cause().getMessage());
//						}
//					});
//				}
//			}else{
//				logger.error("----############---- sql:"+sql+" params:"+params.toString()+" "+checkRes.cause().getMessage());
//				checkRes.cause().printStackTrace();
//				fut.fail(checkRes.cause().getMessage());
//			}
//		});
//		return fut.future();
//	}

}
